import tkinter as tk
from tkinter import ttk, messagebox
from src.service.connection_manager import ConnectionManager
from src.service.meta_service import MetaService
from src.service.data_service import DataService
from src.ui.crud_dialog import open_crud_dialog

class PostgreSQLWindow:
    def __init__(self, root):
        self.root = root
        self.root.title("PostgreSQL GUI")
        self.root.geometry("1200x800")
        self.root.minsize(900, 600)

        # --------------- business object ---------------
        self.conn_mgr = ConnectionManager()
        self.meta_svc: MetaService | None = None
        self.data_svc: DataService | None = None

        self.current_table = None   # schema.table
        # --------------- Interface initialization ---------------
        self._build_ui()

    # ------------------- Interface code -------------------
    def _build_ui(self):
        # ---------- Style ----------
        style = ttk.Style()
        style.theme_use("clam")

        # ---------- 主 Notebook ----------
        self.nb = ttk.Notebook(self.root)
        self.nb.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)

        # 0) Login page
        self.pg_login = ttk.Frame(self.nb)
        self.nb.add(self.pg_login, text="Login")

        # 1) Data management page
        self.pg_work = ttk.Frame(self.nb)
        self.nb.add(self.pg_work, text="Data management")

        # ---------------- Login page ----------------
        lf = ttk.LabelFrame(self.pg_login, text="Database login")
        lf.pack(fill=tk.X, padx=50, pady=80)
        ttk.Label(lf, text="User name:").grid(row=0, column=0, sticky=tk.W, padx=10, pady=10)
        ttk.Label(lf, text="Password:").grid(row=1, column=0, sticky=tk.W, padx=10, pady=10)
        self.user_ent = ttk.Entry(lf)
        self.pwd_ent = ttk.Entry(lf, show="*")
        self.user_ent.grid(row=0, column=1, sticky=tk.EW, padx=10)
        self.pwd_ent.grid(row=1, column=1, sticky=tk.EW, padx=10)
        self.user_ent.insert(0, "postgres")
        lf.columnconfigure(1, weight=1)
        ttk.Button(self.pg_login, text="Login", command=self.login).pack(pady=20)

        # ---------------- Data management page ----------------
        # left: tree
        left = ttk.Frame(self.pg_work, width=220)
        left.pack(side=tk.LEFT, fill=tk.Y, padx=(0, 5), pady=5)
        self.tree = ttk.Treeview(left, show="tree")
        self.tree.pack(fill=tk.BOTH, expand=True)
        self.tree.bind("<<TreeviewSelect>>", self.on_tree_select)

        # Right: upper and lower layers
        right_pane = ttk.PanedWindow(self.pg_work, orient=tk.VERTICAL)
        right_pane.pack(side=tk.RIGHT, fill=tk.BOTH, expand=True, pady=5)

        # Top right: data display area
        top_frame = ttk.LabelFrame(right_pane, text="Data browsing")
        self.result_tree = ttk.Treeview(top_frame, show="headings")
        self.result_tree.pack(fill=tk.BOTH, expand=True)
        for orient in ('y', 'x'):
            bar = ttk.Scrollbar(top_frame, orient=orient.replace('y', 'vert').replace('x', 'horiz'),
                                command=getattr(self.result_tree, f'{orient}view'))
            bar.pack(side=(tk.RIGHT if orient == 'y' else tk.BOTTOM), fill=orient)
            self.result_tree.configure(**{f'{orient}scrollcommand': bar.set})
        right_pane.add(top_frame, weight=4)

        # Bottom right: CRUD button area-Add complex query
        bottom_frame = ttk.LabelFrame(right_pane, text="data manipulation")
        btn_container = ttk.Frame(bottom_frame)
        btn_container.pack(fill=tk.X, padx=10, pady=10)

        # Add a complex query button to the right of the delete button.
        buttons = [
            ("Query", self.on_query),
            ("Insert", self.on_insert),
            ("Update", self.on_update),
            ("Delete", self.on_delete),
            ("Complex Query", self.open_complex_query_dialog)
        ]

        for idx, (txt, cmd) in enumerate(buttons):
            btn = ttk.Button(btn_container, text=txt, command=cmd)
            btn.grid(row=0, column=idx, sticky='ew', padx=2)
            btn_container.columnconfigure(idx, weight=1)

        right_pane.add(bottom_frame, weight=1)

        # status bar
        self.stat = tk.StringVar(value="Status: Please login")
        ttk.Label(self.root, textvariable=self.stat, relief=tk.SUNKEN, anchor=tk.W).pack(side=tk.BOTTOM, fill=tk.X)

    # ================= Login =================
    def login(self):
        """Login method-add API startup"""
        try:
            user = self.user_ent.get()
            password = self.pwd_ent.get()

            conn, dbname = self.conn_mgr.login(user, password)
            self.meta_svc = MetaService(conn)
            self.data_svc = DataService(conn)
            self.stat.set(f"Status: Connected to the database '{dbname}'")
            messagebox.showinfo("Success", "Login successful!")

            # === Add: start API service ===
            self._start_api_service(user, password)

            self._switch_to_work_page()
            self._load_tree()
        except Exception as e:
            messagebox.showerror("Login failed", str(e))

    # ================= Tree =================
    def _load_tree(self):
        self.tree.delete(*self.tree.get_children())
        db_node = self.tree.insert("", tk.END, text=self.conn_mgr.current_db, open=True)
        for schema in self.meta_svc.list_schemas():
            sc_node = self.tree.insert(db_node, tk.END, text=schema, open=True)
            for tbl in self.meta_svc.list_tables(schema):
                self.tree.insert(sc_node, tk.END, text=tbl, tags=("table",))

    # ================= Table selection =================
    def on_tree_select(self, _event):
        sel = self.tree.selection()
        if not sel:
            return
        item = self.tree.item(sel[0])
        if "table" not in item.get("tags", []):
            return
        schema = self.tree.item(self.tree.parent(sel[0]))["text"]
        table = item["text"]
        self.current_table = f"{schema}.{table}"
        self._show_table_data()

    # ================= Show data =================
    def _show_table_data(self, where_dict=None):
        if not self.data_svc:
            return
        cols, rows = self.data_svc.query(self.current_table, where_dict or {})
        self.result_tree.delete(*self.result_tree.get_children())
        self.result_tree["columns"] = cols
        for c in cols:
            self.result_tree.heading(c, text=c)
            self.result_tree.column(c, width=120)
        for row in rows:
            self.result_tree.insert("", tk.END, values=row)
        self.stat.set(f"Status: {len(rows)} records loaded.")

    # ================= Four major operations =================
    def on_query(self):
        if not self.current_table:
            return messagebox.showwarning("point out", "Please select a table first.")
        cols = self.meta_svc.get_columns(self.current_table)
        data = open_crud_dialog(self.root, title=f"query {self.current_table}",
                              fields=[(n, s) for n, _, s in cols],
                              data=None, readonly_cols=[])
        if data is None:
            return
        self._show_table_data({k: v for k, v in data.items() if v != ""})

    def on_insert(self):
        if not self.current_table:
            return messagebox.showwarning("point out", "Please select a table first.")
        cols = self.meta_svc.get_columns(self.current_table)
        pks = set(self.meta_svc.get_primary_keys(self.current_table))
        readonly = {col for col, _, is_serial in cols if is_serial} | pks
        data = open_crud_dialog(self.root, title=f"insert {self.current_table}",
                              fields=[(n, s) for n, _, s in cols],
                              data=None, readonly_cols=readonly)
        if data is None:
            return
        insert_dict = {k: v for k, v in data.items() if k not in readonly or v != ""}
        try:
            self.data_svc.insert(self.current_table, insert_dict)
            messagebox.showinfo("Success", "Inserted successfully!")
            self._show_table_data()
        except Exception as e:
            messagebox.showerror("Insert failed", str(e))

    def on_update(self):
        if not self.current_table:
            return messagebox.showwarning("point out", "Please select a table first.")
        selection = self.result_tree.selection()
        if not selection:
            return messagebox.showwarning("point out", "Please select the line to modify first.")
        pks = self.meta_svc.get_primary_keys(self.current_table)
        if not pks:
            return messagebox.showerror("error", "The table has no primary key, and the row cannot be located.")
        cols = self.meta_svc.get_columns(self.current_table)
        origin = dict(zip(self.result_tree["columns"],
                          self.result_tree.item(selection[0], "values")))
        pk_dict = {pk: origin[pk] for pk in pks}
        readonly = {col for col, _, is_serial in cols if is_serial} | set(pks)
        data = open_crud_dialog(self.root, title=f"modify {self.current_table}",
                              fields=[(n, s) for n, _, s in cols],
                              data=origin, readonly_cols=readonly)
        if data is None:
            return
        update_dict = {k: v for k, v in data.items() if v != origin[k]}
        if not update_dict:
            return messagebox.showinfo("point out", "No fields have been modified.")
        try:
            self.data_svc.update(self.current_table, update_dict, pk_dict)
            messagebox.showinfo("Success", "The modification was successful!")
            self._show_table_data()
        except Exception as e:
            messagebox.showerror("Modification failed", str(e))

    def on_delete(self):
        if not self.current_table:
            return messagebox.showwarning("point out", "Please select a table first.")
        selection = self.result_tree.selection()
        if not selection:
            return messagebox.showwarning("point out", "Please select the line to delete first.")
        if not messagebox.askyesno("Sure", "Are you sure you want to delete the selected line?"):
            return
        pks = self.meta_svc.get_primary_keys(self.current_table)
        if not pks:
            return messagebox.showerror("Error", "The table has no primary key, and the row cannot be located.")
        origin = dict(zip(self.result_tree["columns"],
                          self.result_tree.item(selection[0], "values")))
        pk_dict = {pk: origin[pk] for pk in pks}
        try:
            self.data_svc.delete(self.current_table, pk_dict)
            messagebox.showinfo("Success", "Delete successfully!")
            self._show_table_data()
        except Exception as e:
            messagebox.showerror("Delete failed", str(e))

    # ------------------- Auxiliary function -------------------
    def _switch_to_work_page(self):
        self.nb.select(1)

    # add complex query
    def open_complex_query_dialog(self):
        """Open the complex query dialog box"""
        if not self.data_svc:
            messagebox.showwarning("point out", "Please log in to the database first.")
            return

        dialog = tk.Toplevel(self.root)
        dialog.title("Complex Query")
        dialog.geometry("500x400")
        dialog.transient(self.root)
        dialog.grab_set()

        main_frame = ttk.Frame(dialog, padding="10")
        main_frame.pack(fill=tk.BOTH, expand=True)

        ttk.Label(main_frame, text="Select query type:").grid(row=0, column=0, sticky=tk.W, pady=5)
        self.complex_query_var = tk.StringVar()
        query_combo = ttk.Combobox(main_frame, textvariable=self.complex_query_var, width=40)
        query_combo['values'] = [
            "Monthly income calculation of departments",
            "Patient's medical history",
            "Search for patients"
        ]
        query_combo.grid(row=0, column=1, sticky=tk.EW, pady=5)
        query_combo.bind('<<ComboboxSelected>>', lambda e: self._update_query_params(main_frame))

        # Parametric framework
        param_frame = ttk.LabelFrame(main_frame, text="Query parameters")
        param_frame.grid(row=1, column=0, columnspan=2, sticky=tk.NSEW, pady=10)
        self.param_container = ttk.Frame(param_frame)
        self.param_container.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)

        # Button frame
        btn_frame = ttk.Frame(main_frame)
        btn_frame.grid(row=2, column=0, columnspan=2, pady=10)
        ttk.Button(btn_frame, text="Execute query", command=lambda: self._execute_complex_query(dialog)).pack(side=tk.LEFT,
                                                                                                         padx=5)
        ttk.Button(btn_frame, text="Cancel", command=dialog.destroy).pack(side=tk.LEFT, padx=5)

        # Configuration weight
        main_frame.columnconfigure(1, weight=1)
        main_frame.rowconfigure(1, weight=1)
        param_frame.columnconfigure(0, weight=1)
        param_frame.rowconfigure(0, weight=1)

    def _update_query_params(self, parent_frame):
        """Update query parameter input box"""
        # Clear the existing parameter input box
        for widget in self.param_container.winfo_children():
            widget.destroy()

        query_name = self.complex_query_var.get()
        self.param_entries = {}

        # Create a parameter input box according to the query type
        params_config = {
            "Monthly income calculation of departments": [("Department ID", "dept_id"), ("Year", "year"), ("Month", "month")],
            "Patient's medical history": [("Patient ID", "patient_id")],
            "Search for patients": [("Name keyword", "name_pattern")]
        }

        if query_name in params_config:
            params = params_config[query_name]
            for i, (label, param_name) in enumerate(params):
                ttk.Label(self.param_container, text=label).grid(row=i, column=0, sticky=tk.W, padx=5, pady=5)
                entry = ttk.Entry(self.param_container, width=30)
                entry.grid(row=i, column=1, sticky=tk.EW, padx=5, pady=5)
                self.param_entries[param_name] = entry

        self.param_container.columnconfigure(1, weight=1)

    def _execute_complex_query(self, dialog):
        """Execute complex queries"""
        if not hasattr(self, 'complex_query_var') or not self.complex_query_var.get():
            messagebox.showwarning("point out", "Please select a query type first.")
            return

        query_name = self.complex_query_var.get()

        # Mapping query names to internal query keys
        query_mapping = {
            "Monthly income calculation of departments": "dept_monthly_revenue",
            "Patient's medical history": "patient_medical_history",
            "Search for patients": "search_patients"
        }

        query_key = query_mapping.get(query_name)
        if not query_key:
            messagebox.showerror("error", "Unknown query type")
            return

        # Collecting parameters
        params = {}
        for param_name, entry in getattr(self, 'param_entries', {}).items():
            value = entry.get().strip()
            if value:
                if param_name == 'name_pattern' and value:
                    params[param_name] = f'%{value}%'
                elif 'date' in param_name and value:
                    params[param_name] = value
                else:
                    try:
                        params[param_name] = int(value)
                    except ValueError:
                        params[param_name] = value

        try:
            columns, rows = self.data_svc.execute_complex_query(query_key, params)

            self.result_tree.delete(*self.result_tree.get_children())
            self.result_tree["columns"] = columns

            for col in columns:
                self.result_tree.heading(col, text=col)
                self.result_tree.column(col, width=120, anchor=tk.CENTER)

            for row in rows:
                self.result_tree.insert("", tk.END, values=row)

            self.stat.set(f"Status: The complex query' {query_name}' returned {len(rows)} records.")

            dialog.destroy()

            messagebox.showinfo("Success", f"The query is completed, {len(rows)} records are returned.")

        except Exception as e:
            messagebox.showerror("Query failed", f"Error executing complex query:\n{str(e)}")

    def _start_api_service(self, user: str, password: str):
        """Start API server"""
        try:
            from src.api.hospital_api import HospitalAPI

            self.api = HospitalAPI(user, password)
            if self.api.start():
                self.stat.set(f"Status: connected to the database, and API service is running at http://localhost:5000.")
                print("The API service has started.")
            else:
                self.stat.set(f"Status: database connection succeeded, but API startup failed.")
                print("API start failed.")
        except Exception as e:
            print(f"API start error: {e}")